﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using MDL;

namespace DAL
{
    public class DAL_ConsumerDetails
    {
        /// <summary>
        /// 查询商品消费
        /// </summary>
        /// <param name="cd"></param>
        /// <returns></returns>
        public static List<ConsumerDetail> select_ConsumerDetail(ConsumerDetail cd)
        {
            string sql = string.Format(@"select * from Tables t inner join ConsumerBill cb on t.TableID=cb.TableID inner join ConsumerDetail cd on
 cb.CBID=cd.CBID inner join Products p on p.ProductID=cd.ProdcutID where 1=1 ");
            List<SqlParameter> list = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(cd.TableName))
            {
                sql += " and t.TableName like @Tname";
                list.Add(new SqlParameter("@Tname", "%" + cd.TableName + "%"));
            }
            if (!string.IsNullOrEmpty(cd.ProductName))
            {
                sql += " and p.ProductName like @Pname";
                list.Add(new SqlParameter("@Pname", "%" + cd.ProductName + "%"));
            }
            if (!string.IsNullOrEmpty(cd.CBID))
            {
                sql += " and cb.CBID = @ID";
                list.Add(new SqlParameter("@ID", cd.CBID));
            }
            if (!string.IsNullOrEmpty(cd.CBStartDate.ToString()) && !string.IsNullOrEmpty(cd.CBEndDate.ToString()))
            {
                sql += " and cb.CBStartDate>=@SDate and cb.CBEndDate<=@EDate";
                list.Add(new SqlParameter("@SDate", cd.CBStartDate));
                list.Add(new SqlParameter("@EDate", cd.CBEndDate));
            }
            List<ConsumerDetail> CB = new List<ConsumerDetail>();
            SqlDataReader sr = DBHelper.白道(sql, list.ToArray(),CommandType.Text);
            while (sr.Read())
            {
                ConsumerDetail c = new ConsumerDetail();
                c.CDID = Convert.ToInt32(sr["CDID"]);
                c.CBID = sr["CBID"].ToString();
                c.ProductID = Convert.ToInt32(sr["ProdcutID"]);
                c.CDPrice = float.Parse(sr["CDPrice"].ToString());
                c.CDNum = Convert.ToInt32(sr["CDAmount"]);
                c.CDSale = float.Parse(sr["CDSale"].ToString());
                c.CDMoney = float.Parse(sr["CDMoney"].ToString());
                c.CDType = Convert.ToInt32(sr["CDType"]);
                c.CDDate = (sr["CDDate"].ToString());
                c.CBStartDate = (sr[10].ToString());
                c.CBEndDate = (sr[11].ToString());
                c.TableName = sr["TableName"].ToString();
                if (Convert.ToInt32(sr["CDType"]) == 1)
                {
                    c.ProductName = sr["ProductName"].ToString() + "(退)";
                }
                else
                {
                    c.ProductName = sr["ProductName"].ToString();
                }
                c.CBSale = float.Parse(sr["CBSale"].ToString());
                CB.Add(c);
            }
            return CB;
        }
        /// <summary>
        /// 根据餐桌ID获取消费详细信息
        /// </summary>
        /// <param name="TableId">餐桌ID</param>
        /// <returns>餐桌的消费详细信息</returns>
        public static List<ConsumerDetail> SelectConsumerDetails(int TableId)
        {
            string sql = @"select ProdcutID,ProductName=(select ProductName from Products where ProductID=ProdcutID),CDAmount,CDPrice,CDMoney,CDDate,CDType from ConsumerDetail where Cbid in 
            (select CBID from ConsumerBill where TableID=@TableID and CBClose=0)";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@TableID",TableId)
                                  };
            List<ConsumerDetail> list = new List<ConsumerDetail>();
            using (SqlDataReader sdr = DBHelper.白道(sql, sprs, CommandType.Text))
            {
                while (sdr.Read())
                {
                    ConsumerDetail cd = new ConsumerDetail();
                    cd.ProductID = Convert.ToInt32(sdr[0]);
                    cd.ProductName = sdr[1].ToString();
                    cd.CDNum = Convert.ToInt32(sdr[2]);
                    cd.CDPrice = Convert.ToDouble(sdr[3]);
                    cd.CDMoney = Convert.ToDouble(sdr[4]);
                    cd.CDDate = sdr[5].ToString();
                    cd.CDType = Convert.ToInt32(sdr[6]);
                    list.Add(cd);
                }
            }
            return list;
        }
        /// <summary>
        /// 修改正常菜品退菜
        /// </summary>
        /// <param name="CBID">账单编号</param>
        /// <param name="Pid">菜品ID</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateConsumerDetail(string CBID,int Pid)
        {
            string sql = "update ConsumerDetail set CDType=1 where CBID = @CBID and ProdcutID =@Pid";
            SqlParameter[] sprs = { 
                                  new SqlParameter ("@CBID",CBID),
                                  new SqlParameter ("@Pid",Pid)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 增加保存菜品信息
        /// </summary>
        /// <param name="c">菜品信息对象</param>
        /// <returns>受影响的行数</returns>
        public static int AddConsumerDetail(ConsumerDetail c)
        {
            string sql = "insert into ConsumerDetail values(@CBID,@Pid,@CDprice,@CDmount,0,@CDmoney,0,GETDATE())";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@CBID",c.CBID),
                                  new SqlParameter("@Pid",c.ProductID),
                                  new SqlParameter("@CDprice",c.CDPrice),
                                  new SqlParameter("@CDmount",c.CDNum),
                                  new SqlParameter("@CDmoney",c.CDMoney)
                                  };
            return DBHelper.增删改(sql,sprs,CommandType.Text);
        }
        /// <summary>
        /// 根据账单编号查询详细消费
        /// </summary>
        /// <param name="CBID">账单编号</param>
        /// <returns>详细消费</returns>
        public static List<ConsumerDetail> SelectXX(string CBID)
        {
            string sql = @"select * from ConsumerDetail c inner join Products p on 
                        c.ProdcutID = p.ProductID where CBID = @CBID";
            SqlParameter[] sprs = { new SqlParameter("@CBID", CBID) };
            List<ConsumerDetail> list = new List<ConsumerDetail>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs, CommandType.Text))
            {
                while (dr.Read())
                {
                    ConsumerDetail c = new ConsumerDetail();
                    c.ProductName = dr[10].ToString(); ;
                    c.CDPrice = Convert.ToInt32(dr[3]);
                    c.CDNum = Convert.ToInt32(dr[4]);
                    c.CDMoney = Convert.ToDouble(dr[6]);                    
                    list.Add(c);
                }
            }
            return list;
        }
        /// <summary>
        /// 修改已点菜品信息状态
        /// </summary>
        /// <param name="CBID">账单编号</param>
        /// <param name="ProdcutID">产品ID</param>
        /// <returns></returns>
        public static int UpdateConsumerDetailCDType(string CBID, int ProdcutID)
        {
            string sql = "update ConsumerDetail set CDType=1 where CBID=@CBID and ProdcutID=@ProdcutID";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@CBID",CBID),
                                  new SqlParameter("@ProdcutID",ProdcutID)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
    }
}
